package dfbz.com.dao;

import dfbz.com.model.Dept;
import dfbz.com.util.JDBCUtil;
import org.apache.commons.dbutils.BasicRowProcessor;
import org.apache.commons.dbutils.GenerousBeanProcessor;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.RowProcessor;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;

import java.sql.SQLException;
import java.util.List;
import java.util.Map;

public class DeptDao {
    public  List<Map<String,Object>> findAll() {
        QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());

        List<Map<String,Object>> deptList = null;
        try {
            deptList = qr.query("select * from dept", new MapListHandler());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return deptList;
    }

    public List<Map<String, Object>> showDept(){
        QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
        List<Map<String, Object>> dept=null;
        String sql="select d.id,d.name,u.dept_id as did,count(name) as total\n" +
                "from dept d\n" +
                "left join user u on u.dept_id=d.id\n" +
                "group by name\n" +
                "order by d.id";
        try {
            dept = qr.query(sql, new MapListHandler());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return dept;
    }

    public List<Map<String, Object>> showWorkers(Integer did) {
        QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
        List<Map<String, Object>> workers=null;
    String sql="select u.id,u.username as name,u.dept_id as did,uu.real_name as realname\n" +
            "from user u \n" +
            "left join dept d on d.id=u.dept_id\n" +
            "left join user_info uu on u.id=uu.user_id\n" +
            "where u.dept_id=?";

        try {
            workers = qr.query(sql, new MapListHandler(), did);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return  workers;

    }


    public void updateDept(Integer userId, Integer deptId) {
        QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
        String sql="update user set dept_id=? where id=?";
        try {
            qr.update(sql,userId);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
